package fy.uap.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import org.apache.commons.logging.LogFactory;
import org.json.JSONObject;

import fy.config.FyConfig;
import fy.util.DBUtil;

public class FinanceProviderMergerDao {
	private static org.apache.commons.logging.Log log = LogFactory
			.getLog(FinanceProviderMergerDao.class);

	/**
	 * 通过供应商编码查询供应商名称
	 * 
	 * @param code
	 * @param pk_corp
	 * @return
	 */
	private static final String querySQL ="select v.cvenname from fy_supplier_vendor v right join fy_supplier_vendor_invclass vi on v.pk_vendor = vi.pk_vendor left join fy_supplier_invclass i on vi.pk_invclass = i.pk_invclass where vi.denddate is null  and vi.dr <> 1 and v.dr <> 1 and i.dr <> 1 and v.cvendefine1 = 'financial' and (vi.pk_corp =? or 'true'=?) and v.cvencode =?";
	//原查询语句
	//"select v.cvenname from fy_supplier_vendor v right join fy_supplier_vendor_invclass vi on v.pk_vendor = vi.pk_vendor left join fy_supplier_invclass i on vi.pk_invclass = i.pk_invclass where v.denddate is null and i.cinvclasscode = 'NONE' and vi.dr <> 1 and v.dr <> 1 and i.dr <> 1 and v.cvendefine1 = 'financial' and (vi.pk_corp = ? or 'true'=?) and v.cvencode = ?"
	public static String queryFinanceProviderNameByCode(String code,
			String pk_corp, String isNew) {
		Connection conn = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		String result = null;
		try {
			conn = DBUtil.connect(FyConfig.getUAP_DB_CONFIG());

			pst = conn
					.prepareStatement(querySQL);

			pst.setString(3, code);
			pst.setString(2, isNew);
			pst.setString(1, pk_corp);

			rs = pst.executeQuery();
			JSONObject jo = new JSONObject();
			if (rs.next()) {
				jo.put("success", true);
				jo.put("result", rs.getString(1));
			} else {
				jo.put("success", false);
				jo.put("result",
						"未查询到供应商编码对应的供应商，可能原因：\n1、供应商编码输入错误；\n2、用户所在制造部没有导入该供应商；\n3、该供应商已被停用。");
			}
			result = jo.toString();
		} catch (Exception e) {
			e.printStackTrace();
			log.error("查询供应商名称发生异常", e);
			result = "{success : false, result : '查询供应商名称发生异常'}";
		} finally {
			DBUtil.close(rs, pst, conn);
		}
		return result;
	}

	/**
	 * 查询供应商名称是否已经存在
	 * 
	 * @param supplierNameArray
	 *            String[]
	 * @return
	 */
	public static String checkProviderNameExist(String[] supplierNameArray) {
		Connection conn = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		String result = null;
		try {
			conn = DBUtil.connect(FyConfig.getUAP_DB_CONFIG());

			StringBuilder sb = new StringBuilder()
					.append("select v.cvencode,v.cvenname from fy_supplier_vendor v where  v.cvendefine1 = 'financial' and v.cvenname in ( ? ");

			for (int i = 1, length = supplierNameArray.length; i < length; i++) {
				sb.append(",?");
			}
			sb.append(")");

			pst = conn.prepareStatement(sb.toString());

			for (int i = 0, length = supplierNameArray.length; i < length; i++) {
				pst.setString(i + 1, supplierNameArray[i]);
			}

			rs = pst.executeQuery();
			JSONObject jo = new JSONObject();
			boolean success = true;
			sb = new StringBuilder();
			while (rs.next()) {
				sb.append("供应商“").append(rs.getString(2)).append("”已存在，供应商编码：")
						.append(rs.getString(1)).append("\n");
				success = false;
			}
			jo.put("success", success);
			jo.put("result", sb.toString());
			result = jo.toString();
		} catch (Exception e) {
			e.printStackTrace();
			log.error("校验供应商名称发生异常", e);
			result = "{success : false, result : '校验供应商名称发生异常'}";
		} finally {
			DBUtil.close(rs, pst, conn);
		}
		return result;
	}

	/**
	 * 查询供应商编码是否已经存在
	 * 
	 * @param code
	 * @param pk_corp
	 * @return
	 */
	public static String checkProviderCodeExist(String[] supplierCodeArray) {
		Connection conn = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		String result = null;
		try {
			conn = DBUtil.connect(FyConfig.getUAP_DB_CONFIG());

			StringBuilder sb = new StringBuilder()
					.append("select v.cvencode,v.cvenname from fy_supplier_vendor v where v.cvencode in ( ? ");

			for (int i = 1, length = supplierCodeArray.length; i < length; i++) {
				sb.append(",?");
			}
			sb.append(")");

			pst = conn.prepareStatement(sb.toString());

			for (int i = 0, length = supplierCodeArray.length; i < length; i++) {
				pst.setString(i + 1, supplierCodeArray[i]);
			}

			rs = pst.executeQuery();
			JSONObject jo = new JSONObject();
			boolean success = true;
			sb = new StringBuilder();
			while (rs.next()) {
				sb.append("供应商编码“").append(rs.getString(1))
						.append("”已存在，供应商名称：").append(rs.getString(2))
						.append("\n");
				success = false;
			}
			jo.put("success", success);
			jo.put("result", sb.toString());
			result = jo.toString();
		} catch (Exception e) {
			e.printStackTrace();
			log.error("校验供应商编码发生异常", e);
			result = "{success : false, result : '校验供应商编码发生异常'}";
		} finally {
			DBUtil.close(rs, pst, conn);
		}
		return result;
	}
}
